from flask_records.decorators import query_by_page, query

GET_BY_PAGE_SQL = """
select *
from category
where (is_active = :is_active or :is_active is null)
  and (
            lower(category.name) like lower(:search_str_re) or
            lower(category.short_name) like lower(:search_str_re) or
            :search_str_re is null
    )
  and (parent_id = :parent_id or :parent_id is null)      
"""


def get_by_page(page, page_size, sorted_by, sorted_by_type, search_str, **kwargs):
    sql = GET_BY_PAGE_SQL
    order_by_sql = "{} ORDER BY {} {}".format(sql, sorted_by, sorted_by_type)

    if search_str:
        # 以空格分割，搜索所有包含的
        search_str_re = '%{}%'.format('%'.join(search_str.split(" ")))
    else:
        search_str_re = None

    @query_by_page(order_by_sql, page_size=page_size)
    def _get_by_pagination(
            page, search_str_re, **kwargs):
        pass

    return _get_by_pagination(
        page, search_str_re, **kwargs
    )


GET_CATEGORY_CHILDREN_RECURSIVE = """
WITH RECURSIVE T AS (
  SELECT *
  FROM category
  WHERE id= :parent_id 
  union ALL
  SELECT category.*
  FROM category,
    T
  WHERE category.parent_id = T.id
)
SELECT T.id, T.name, T.parent_id
FROM T
ORDER BY name
"""


def get_category_children_recursive(parent_id):
    sql = GET_CATEGORY_CHILDREN_RECURSIVE

    @query(sql)
    def _get(parent_id):
        pass

    return _get(parent_id)


# 遍历所有类别, 按层级关系排序
GET_ALL_CATEGORY = """
    WITH RECURSIVE T (id, name, parent_id, PATH, DEPTH)  AS (
      SELECT id, name, parent_id, ARRAY[ID] AS PATH, 1 AS DEPTH
      FROM category
      WHERE parent_id IS NULL 
    
      UNION ALL
    
      SELECT  D.id, D.name, D.parent_id, T.PATH || D.id, T.DEPTH + 1 AS DEPTH
      FROM category D
        JOIN T ON D.parent_id = T.id
    )
    SELECT T.id, T.name, T.parent_id, C2.name as parent_name, C3.is_active, C3.notes, PATH, DEPTH FROM T
      left join category as C2 on T.parent_id = C2.id
      left join category as C3 on T.id = C3.id
    WHERE case when :is_active isnull then 1=1 else C3.is_active = :is_active end
      
    ORDER BY depth;
"""


def get_all(is_active=None):
    sql = GET_ALL_CATEGORY

    @query(sql)
    def _get(is_active):
        pass

    return _get(is_active)